﻿Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Data
Imports System.Collections.Generic

Namespace Pic
    Public Class Categories
        Inherits clsBase
#Region "Private Variables"
        Private _Id As Integer
        Private _Name As String
        Private _Description As String
        Private _CategoryId As Integer
#End Region
#Region "Properties"
        Public Property Id As Integer
            Set(value As Integer)
                _Id = value
            End Set
            Get
                Return _Id
            End Get
        End Property
        Public Property Name As String
            Set(value As String)
                _Name = value
            End Set
            Get
                Return _Name
            End Get
        End Property
        Public Property Description As String
            Set(value As String)
                _Description = value
            End Set
            Get
                Return _Description
            End Get
        End Property
        Public Property CategoryId As Integer
            Set(value As Integer)
                _CategoryId = value
            End Set
            Get
                Return _CategoryId
            End Get
        End Property
#End Region
#Region "Constructor"
        Public Sub New(ByVal Id_ As Integer, ByVal Name_ As String, ByVal Description_ As String, ByVal CategoryId_ As Integer)
            Me.Id = Id_
            Me.Name = Name_
            Me.Description = Description_
            Me._CategoryId = CategoryId_
        End Sub
#End Region
#Region "Methods"
        Private Shared Function GetCategoriesFromReader(ByVal reader_ As IDataReader) As Categories
            Dim rtrn As Categories = New Categories(reader_("Id"), reader_("Name"), reader_("Description"), reader_("CategoryId"))
            Return rtrn
        End Function
        Public Shared Function GetCategoryById(ByVal Id_ As Integer) As Categories
            Dim conn As New SqlConnection(ConnectionString)
            Dim veriString As String = "Select * from tbl_Categories where id=@Id"
            Dim reader As IDataReader
            Try
                conn.Open()
                Dim cmd As New SqlCommand(veriString, conn)
                cmd.CommandType = CommandType.Text

                cmd.Parameters.Add("@Id", SqlDbType.Int).Value = Id_
                reader = ExecuteReader(cmd, CommandBehavior.SingleRow)
                If reader.Read Then
                    Return GetCategoriesFromReader(reader)
                Else
                    Return Nothing
                End If
            Catch ex As Exception
                Throw ex
            Finally
                conn.Close()
            End Try
        End Function
        Private Shared Function GetCategoriesListFromReader(ByVal reader As IDataReader) As List(Of Categories)
            Dim rtrnList As New List(Of Categories)
            Do While reader.Read
                rtrnList.Add(GetCategoriesFromReader(reader))
            Loop
            Return rtrnList
        End Function
        Public Shared Function GetCategoriesListByFilter(ByVal Filtre_ As String, ByVal FiltreOrder_ As String) As List(Of Categories)
            Dim conn As New SqlConnection(ConnectionString)
            Dim veriString As String = "select * from website1.dbo.tbl_Categories where " & Filtre_ & "' order by " & FiltreOrder_ & " asc"
            Try
                conn.Open()
                Dim cmd As New SqlCommand(veriString, conn)
                cmd.CommandType = CommandType.Text
                Return GetCategoriesListFromReader(ExecuteReader(cmd, CommandBehavior.Default))
            Catch ex As Exception
                Throw ex
            Finally
                conn.Close()
            End Try
        End Function
        Public Shared Function InsertCategory(ByVal Name_ As String, ByVal Description_ As String, ByVal CategoryID_ As Integer) As Integer
            Dim conn As New SqlConnection(ConnectionString)
            Dim veriString As String = "insert into tbl_Categories(name,description,category_id) values(@Name,@Description,@CategoryID)"
            Try
                conn.Open()
                Dim cmd As New SqlCommand(veriString, conn)
                cmd.CommandType = CommandType.Text
                cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = Name_
                cmd.Parameters.Add("@Description", SqlDbType.Text).Value = Description_
                cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = CategoryID_
                Return ExecuteNonQuery(cmd)
            Catch ex As Exception
                Throw ex
            Finally
                conn.Close()
            End Try
        End Function
        Public Shared Function UpdateCategory(ByVal Id_ As Integer, ByVal Name_ As String, ByVal Description_ As String, ByVal CategoryID_ As Integer) As Integer
            Dim conn As New SqlConnection(ConnectionString)
            Dim veriString As String = "update tbl_Categories set Name=@Name, description=@Description, category_id=@CategoryID where id=@Id"
            Try
                conn.Open()
                Dim cmd As New SqlCommand(veriString, conn)
                cmd.CommandType = CommandType.Text
                cmd.Parameters.Add("@Id", SqlDbType.Int).Value = Id_
                cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = Name_
                cmd.Parameters.Add("@Description", SqlDbType.Text).Value = Description_
                cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value = CategoryID_
                Return ExecuteNonQuery(cmd)
            Catch ex As Exception
                Throw ex
            Finally
                conn.Close()
            End Try
        End Function
        Public Shared Function DeleteCategory(ByVal Id_ As Integer) As Boolean
            Dim conn As New SqlConnection(ConnectionString)
            Dim veriString As String = "Select count(1) from tbl_Categories Where id=@Id"
            Dim count As Integer
            Try
                conn.Open()
                Dim cmd As New SqlCommand(veriString, conn)
                cmd.Parameters.Add("@Id", SqlDbType.Int).Value = Id_
                count = ExecuteScalar(cmd)
                If count = 0 Then
                    Return False
                Else
                    cmd.CommandText = "Delete from tbl_Categories Where id=@Id"
                    cmd.CommandType = CommandType.Text
                    cmd.Parameters.Add("@Id", SqlDbType.Int).Value = Id_
                    Return ExecuteNonQuery(cmd)

                End If
            Catch ex As Exception
                Throw ex
            Finally
                conn.Close()
            End Try
        End Function

        'Public Shared Function SilinebilirMi(ByVal ProjeID_ As Integer) As Boolean
        '    Dim conn As New SqlConnection(ConnectionString)
        '    Dim veriString As String = "Select count(1) from tblSondajlar where ProjeID=" & ProjeID_
        '    Dim count As Integer
        '    Try
        '        conn.Open()
        '        Dim cmd As New SqlCommand(veriString, conn)
        '        count = ExecuteScalar(cmd)
        '        If count = 0 Then
        '            Return True
        '        Else
        '            Return False
        '        End If
        '    Catch ex As Exception
        '        Throw ex
        '    Finally
        '        conn.Close()
        '    End Try
        'End Function
#End Region

    End Class
End Namespace


